ついてExcel 入門講座 Home ExcelVBAPartsCollection DownLoad My Profile
Excel Referrence Manual Links Excel Function Manual Myself My BBS


主婦のための Excel 入門講座

(Excelの計算式と関数の入力)

第5回 関数の中に関数を組み込む

今回の主な内容　
1　関数の引数として関数を使う
2　組み込み可能な関数の数
3　ネストされた関数の入力
　(1) 最初の関数の入力
　(2) 2 つ目以降の関数の入力
4　関数の計算式を編集する



1　関数の引数として関数を使う

Shun：今回は、計算式の中で関数の中にさらに関数を組み込む方法について説明しよう。
　関数を使った計算式では、1つの関数だけで計算式が成り立っていることは少いということは前回にも説明したね。「関数の中に関数を組み込む」ということはね、関数の引数としてさらに関数を使うっていうことなんだ。これを「関数のネスト」と呼んでいる。

Shun：上図の下側にあるのが「関数の引数に組み込む(関数のネスト)」という方法なんだけど、図の場合は、ある関数の 1 つ目の引数には何らかの値を入力して、2 つ目の引数と 3 つ目の引数にさらに関数を使っているということを表しているんだ。

優子：関数の引数には、数字や文字などの値のほかに、セルの参照とかセル範囲、計算式も使えるって、確か「番外編(2)」で習ったような気がするけど、計算式の中には関数も含まれるわけね。関数の中に関数を使うから「組み込む」ということなのね。

Shun：そのとおりだよ。特に「IF関数」ではほとんどの場合、複数の関数を組み込んで使うと言ってもいいぐらい、頻繁に利用される方法なんだ。まっ、何はともあれ、実例で見た方が分かり易いね。


2　組み込み可能な関数の数

Shun：「関数のネスト」の意味はさっき説明したとおりだけど、それじゃあどれだけ組み込んでもいいかというと、数の制限があってね、1つの計算式の中で最大 7 段階まで組み込むことができると決まっているんだ。

Shun：これは、地下鉄の料金計算をするための表なんだけど、「乗車距離」の下のセルに距離数を入れると「大人料金」「小児料金」欄の下に料金を求めることができるんだ。計算式はね下図のようになっている。

Shun：この計算式をよく見ると、IF関数が 4 つ使われているのがわかるね。それぞれのIF関数は親子の関係になっているんだよ。下の図にいろいろ番号が付けてあるのは、IF関数とIF関数の関係と、それぞれのIF関数の中の引数の関係を表しているんだ。

　下表の「記号」欄の左側の数字は、数式の中の左から数えたIF関数の位置、右側の○で囲んだ数字は、それぞれのIF関数の引数で、①が「論理式」、②が「真の場合」、③が「偽の場合」を意味している。

関数の位置 記号 それぞれのIF関数の引数の計算式と意味(下段)

最初のIF関数
(第1レベル)

 1-① $C9<=3
論理式：セル「C9」の値(距離)が 3km まで
1-② 200
真の場合：式が成立したとき、料金は　200 円
1-③ IF($C9<=7,230,IF($C9<=11,260,IF($C9<=15,290,320)))
偽の場合：式が成立しなかったとき　2番目のIF関数を実行
2番目のIF関数
(第2レベル)		 2-① $C9<=7
論理式：セル「C9」の値(距離)が 3kmを超え7km まで
2-② 230
真の場合：式が成立したとき、料金は　230 円
2-③ IF($C9<=11,260,IF($C9<=15,290,320))
偽の場合：式が成立しなかったとき　3番目のIF関数を実行
3番目のIF関数
(第3レベル)		 3-① $C9<=11
論理式：セル「C9」の値(距離)が kmを超え11km まで
3-② 260
真の場合：式が成立したとき、料金は　260 円
3-③ IF($C9<=15,290,320)
偽の場合：式が成立しなかったとき　4番目のIF関数を実行
4番目のIF関数
(第4レベル)		 4-① $C9<=15
論理式：セル「C9」の値(距離)が 11kmを超え15km まで
4-② 290
真の場合：式が成立したとき、料金は　290 円
4-③ 320
偽の場合：すべての式が成立しなかったとき(距離が 15kmを超えたとき)、料金は　320 円

Shun：この計算式では、最初のIF関数が大本の関数で、2番目のIF関数が最初のIF関数の「偽の場合」の引数に組み込まれた関数で、いわば子関数になっている。同じように、3番目のIF関数は、2番目のIF関数の「偽の場合」の引数に組み込まれた子関数というようになっている。

　このように、親子関係の階層(レベル)は 7 段階まで指定ができるということなんだね。
　ところが、たとえば3番目のIF関数の引数の「論理式」「真の場合」「偽の場合」の全部に関数を使ったとしても、これはそれぞれが同格レベルの関数なので、みんな第4レベルの関数になるんだよ。

優子：そうかぁ、一つの計算式の中で関数が 7 つしか使えないんじゃなくて、関数の親子の関係が 7 代までという意味なんだね。同じレベルの関数の引数に組み込んだ関数は兄弟みたいなもんだから、レベルはいっしょというわけなんだ。そうよね。

Shun：そうそう。理解が早いね・だから、極端にいうと、関数は、一つの計算式でいくつも使えるけど、親子の関係で言うと 7 代まで、ということになるね。


3　ネストされた関数の入力

Shun：それじゃあ、関数のネストの操作方法を実際にやってみよう。下図はね、家計簿なんだけど、科目コードを入力すると科目名が自動的に表示され、支出金額が入力されると、各科目別の内訳がそれぞれの科目欄に振分け表示することができる表なんだ。

優子：そんな便利なことができるんだぁ。そしたら一々内訳を計算する必要もなくなるっていうことね。早く見てみたいわ。

Shun：この表でデータを入力するところは、「月日」「CD(科目コード)」「収入額」「支出額」の欄だけで、後は計算によって「科目」「残額」と科目別内訳が入力される。まず、計算式を見てみよう。

Shun：図の上の計算式が科目コードを科目名に変換する式で、セル「C3」に入力して下方向へ複写するんだ。この計算式の意味は、後で説明するね。

　図の下の計算式は、科目別内訳を入力する計算式で、セル「G3」に入力してから、右方向に複写し、その複写した結果の全体をさらに下方向に複写する。これは、計算式のある列の1行目の番号と計算式のある行のB列の科目コードが一致したら、同じ行のC列にある支出額を表示させる式なんだ。「$」マークの位置に注意してね。

Shun：次に、残高欄の計算式。図の上の計算式は、残出欄の最初の行だけに入力するもので、単純に収入から支出を差し引いた結果を求めている。図の下の計算式は残高欄の2行目以降に入力する式で、計算式のすぐ上の行の残高に計算式入力行の収入を加えて支出を差し引いた結果を求めている。この式を下方向に必要なだけ複写する。

　IF関数を使ったのは、データが入力されていないときに「 0 (ゼロ)」を表示させないためで、科目コードを入力すると計算が実行されるようにしたわけ。

　この、僅か4つの計算式で家計簿ができてしまうんだよ。

優子：へぇー。家計簿の作り方ってもっと難しいと思ってたのに、こんな簡単なことで出来てしまうなんて・・・科目名は一々入力しなくていいし、しかも、残高や科目別内訳まで支出額の入力と同時に入力されてしまうんでしょ。なんか信じられない。


Shun：では、最初の計算式の説明をするね。下の計算式だったね。
　　=IF(B3="","",HLOOKUP(B3,$G$1:$K$2,2,0))

IF関数の引数 引　数　の　値

意　　　　　味
論理式 B3="" セル「B3」の値が空白
真の場合 "" 条件が成立したら計算式入力セルを空白にする
偽の場合 HLOOKUP(B3,$G$1:$K$2,2,0) 条件が成立しなかったらHLOOKUP関数の式を実行した結果を計算式入力セルの値とする

上図のIF関数の実行により、論理式が成立しないとき(セル「B3」の値が空白でないとき)は、偽の場合の引数(HLOOKUP関数)が実行される。下表はHLOOKUP関数の引数の意味。
HLOOKUP関数の引数

引数の値

意　　　　　　　　　　味
検索値 B3 セル「B3」の値で指定した範囲を検索する
範囲 $G$1:$K$2 セル範囲「$G$1:$K$2」の最初の行に検索値と同じ値があるかどうかを調べる
行番号 2 範囲の中で検索値と同じ値が見つかったら、その列の行番号2のセルの値を答え(戻り値)とする
検索の型 0 検索値と完全に一致するものだけを検索する

Shun：もう一度家計簿を見てみよう。

Shun：上の表では、「科目」欄に計算式が入力されていて、「CD」欄の数値を検索値として緑色のセル範囲(G1:K2)の1行目を調べ、CD欄に入力された数値と同じものがあれば、同じ列の2行目の値(科目名)をC列の科目欄に表示しているんだ。

　たとえば、セル「B7」の値は「4」で、この値は調べる範囲のセル「J1」に同じ数値があるので、同じ列(J列)の2行目の値「教育費」がセル「C7」に入力されるというわけだね。

優子：なるほどね。科目欄の左に科目コードの番号を入力すると緑色のセルの科目別一覧の見出しの番号に同じ物があるかどうかを調べて、あれば、番号の下の科目名をC列の科目欄に表示するっていうわけね。


(1) 最初の関数の入力
Shun：どうやらわかったようだから、関数を入力しよう。まず、最初にIF関数からだね。

Shun：最初に計算式を入力するセルをクリックしてから、ツールバーの「関数の貼付け」アイコンをクリックする。

Shun：「関数の貼付け」ダイアログボックスが表示されたら、「関数の分類(C)」一覧の中から「論理」をクリックして、「関数名(N)」一覧で「IF」関数をクリックした後、「OK」ボタンをクリックする。

Shun：「数式パレット」が表示されたら、「論理式」に「B3=""」と入力する。このとき、折り畳みボタンを使ってセル参照するときの方法は前回やったから、参考にしてね。次に「真の場合」に「""」を入力する。

(2) 2 つ目以降の関数の入力
Shun：「IF関数」の「偽の場合」の引数に「HLOOKUP関数」を指定するんだけど、関数の引数に別の関数を組み込むときは、組み込む場所の引数の入力ボックスをクリック(この場合は、「偽の場合」の入力ボックス)してから、「名前ボックス(関数)」の▼ボタンをクリックすると、最近使った関数の一覧が表示されるんだ。

Shun：上図が最近使った関数の一覧で、この中には「HLOOKUP」関数が表示されているから、その関数名をクリックすると「HLOOKUP関数」の数式パレットが表示される。
　この一覧に指定する関数が無いときは「その他の関数」のところをクリックすると「関数の貼付け」ダイアログボックスが表示されるんだ。ここでは、一覧の中に「HLOOKUP関数」が無いものとして、「その他の関数」を選択することにしよう。

Shun：「IF関数」のときと同じように、「関数の分類(C)」で「検索/行列」を選択して、「関数名(N)」で「HLOOKUP」を選択した後、「OK」ボタンをクリックする。

Shun：これが「HLOOKUP関数」の「数式パレット」。それぞれの引数入力ボックスに引数の値を入力してから、「OK」ボタンをクリックする。

　今回は、組み込む関数が 2 つだけだったから、この画面の「数式パレット」の「OK」ボタンをクリックしたけど、「数式パレット」の「OK」ボタンは、計算式で使う最後の関数の入力が完了したときだけクリックするんだよ。そうでないと、入力途中でも計算式の入力が終わったと見なされてしまうんだ。

Shun：これが計算式の全てを入力し終わったときの「数式バー」の状態。左の「名前ボックス」を見ると、「C3」と書いてあるね。つまり、セル「C3」にはこの計算式が入力されています、ということなんだ。

優子：はい。わかりました。そうするとね先生、関数の中に別の関数を組み込むときは、組み込む引数の入力ボックスをクリックしてから、「名前ボックス」の▼ボタンをクリックして、新しい関数を選択したら同じようにその関数の引数の値を入力すればいいってことね。

　だから、いくつ関数を組み込む場合でも同じやり方で指定すればいいのね。ただ、「数式パレット」の「OK」ボタンは、計算式の最後のときだけクリックするところがミソね。

Shun：そうそう。優子さん上手く説明できるね。今度から僕の代わりに先生を頼もうかなぁ。複数の関数の指定といっても、それほど難しいことじゃないって思ったでしょ。


4　関数の計算式を編集する

優子：先生の代わりだなんてとんでもない。ところで先生、後で計算式を訂正したり編集するにはどうしたらいいのかしら。「数式バー」でなきゃできないんですか？

Shun：関数が入力されている計算式を編集するには、もちろん、「数式バー」で直接編集することもできるんだけど、「数式パレット」を表示させて編集することもできるんだよ。やり方は簡単だからすぐわかると思う。

Shun：「数式パレット」で関数を編集するには、まず、計算式が入力されているセルをクリックして、「数式の編集」ボタンをクリックする。

Shun：第1番目の関数の「数式パレット」が表示されるので、「数式バー」で編集したい関数の名前をクリックすると、その関数の「数式パレット」が表示される。最初の関数に戻るときは同じように「数式バー」で戻る関数の名前をクリックすればいい。

優子：そうすると、数式バーで「数式パレット」を表示したい関数の名前をクリックする度に「数式パレット」の内容が変わるっていうことね。簡単じゃない。（*^_^*）

Shun：さて、これで計算式の指定方法の講座もとりあえず終わりだよ。優子さん、長い間どうもお疲れサンでした。Excel の面白さをわかってくれたかな？

優子：先生、どうもありがとうございました。m(_ _)m ﾍﾟｺｯ。Excel のことは何も知らなかったけど、なんとか自分だけでもやれそうな気がするわ。またわからないことがあったら教えてくださいね。ところで、次の講座の予定はありますか？

Shun：う～～ん。今のところはね家計簿の作り方でもどうか、と思っているんだけど、まだ決めていない。今までの講座ではExcelの機能のほんの僅かなことしかやっていないから、少し考えてみてから次の講座を開こうかと・・・。始めるときはまた連絡するね。

優子：は～～い。楽しみにして待ってまぁ～～す。それじゃ、これで失礼します。


テレワークならECナビ Yahoo 楽天 LINEがデータ消費ゼロで月額500円～！
無料ホームページ 無料のクレジットカード 海外格安航空券　海外旅行保険が無料！ 海外ホテル